<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;


class Report extends Model
{
    public $timestamps = false;

    protected $fillable = ['name','section','squad','group','student','id'];

    public function get_kaoqin(Request $request)
    {
        $builderJson = array();
        $personJson = $squadJson = '';
        $status = array('1'=>'正常','2'=>'请假','3'=>'迟到','4'=>'缺勤','5'=>'家长代课');        //获取个人考勤情况
        $person = DB::table('attendance_records as ar')
            ->join('notices as n','ar.notice_id','=','n.id')
            ->where('ar.student_id',$request['student'])
            ->where('n.notice_type','=','1')
            ->whereIn('n.status',['2','4'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.strtotime($request['startTime']))
            ->groupBy('ar.attendance_status')->select(DB::raw('count(1) as num'),'ar.attendance_status')->get();

        $temp = array();
        $total = 0;

        foreach($status as $v){
            $temp[$v] = 0;
        }
        foreach($person as $v){
            $temp[$status[$v->attendance_status]] = $v->num;
            $total+=$v->num;
        }

        $builderJson['all'] = $total;
        $builderJson['charts'] = $temp;

        $personJson = json_encode($temp);


        //先获取该班下所有的学生
        $students = DB::table('students')->where('squad_id',$request['squad'])->where('status','=','1')
            ->get(['id'])->toArray();
        $stu_id = array();
        foreach($students as $v){
            $stu_id[] = $v->id;
        }
        //班级考勤情况
        $squad = DB::table('attendance_records as ar')
            ->join('notices as n','ar.notice_id','=','n.id')
            ->whereIn('ar.student_id',$stu_id)
            ->where('n.notice_type','=','1')
            ->whereIn('n.status',['2','4'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.strtotime($request['startTime']))
            ->groupBy('ar.attendance_status')->select(DB::raw('count(1) as num'),'ar.attendance_status')->get();

        $temp = array();
        foreach($squad as $v){
            $temp[$status[$v->attendance_status]] = $v->num;
        }
        $squadJson=json_encode($temp);


        $ids = $id= array();
        $id = DB::table('squads')->where('section','=',$request['section'])->get();
        foreach($id as $v){
            $ids[] = $v->id;
        }
        //获取班段下所有学生
        $section_stu = DB::table('students')
            ->whereIn('squad_id',$ids)->where('status','=','1')
            ->get(['id']);
        $stu_id = array();
        foreach($section_stu as $v){
            $stu_id[] = $v->id;
        }

        //获取班段考勤情况
        $squad = DB::table('attendance_records as ar')
            ->join('notices as n','ar.notice_id','=','n.id')
            ->whereIn('ar.student_id',$stu_id)
            ->where('n.notice_type','=','1')
            ->whereIn('n.status',['2','4'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'<='.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)'.'>='.strtotime($request['startTime']))
            ->groupBy('ar.attendance_status')->select(DB::raw('count(1) as num'),'ar.attendance_status')->get();

        $temp = array();
        foreach($squad as $v){
            $temp[$status[$v->attendance_status]] = $v->num;
        }
        $sectionJson=json_encode($temp);

        $memo = array();
        $memo[] = '在'.date('Y/m/d',strtotime($request['startTime'])).'-'.date('Y/m/d',strtotime($request['endTime'])).'时间段内，';
        $memo[] =  '共打考勤{blue|'.$total.'}次'.'你的正常考勤次数为{red|'.$builderJson['charts']['正常'].'}次';
        if($total == 0){
            $memo[] = '出勤率为{red|0%}';
        }else{
            $memo[] = '出勤率为{red|'.round($builderJson['charts']['正常']/$total*100,2).'%}';
        }

        $builderJson=json_encode($builderJson);
        return array('builderJson'=>$builderJson,'personJson'=>$personJson,'squadJson'=>$squadJson,'sectionJson'=>$sectionJson,'memo'=>$memo);
    }

    public function get_ceyan(Request $request)
    {
        //找到测验
        $test_ids = DB::table('test_assigns as ta')->join('notices as tc','ta.id','=','tc.test_assign_id')
            ->where('ta.squad_id',$request['squad'])
            ->where('tc.notice_type','=','2')
            ->whereIn('tc.status',['2','4'])
            ->whereRaw('UNIX_TIMESTAMP(tc.starttime)'.'<='.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(tc.starttime)'.'>='.strtotime($request['startTime']))
            ->select('ta.id as test_assign_id','tc.id as notice_id','ta.test_config_id')->get()->toArray();
        if(count($test_ids) <= 0){
            return array('status'=>-1,'message'=>'没有找到测试','data'=>'');
        }


        //获取到该班级的所有学生
        $student = DB::table('students')->where('squad_id',$request['squad'])->where('status','=','1')
            ->get(['id'])->toArray();
        $students = array();
        foreach($student as $v){
            $students[] = $v->id;
        }

        $id_arr = array();
        foreach($test_ids as $v){
            $id_arr[] = $v->notice_id;
        }

        $test_rule = DB::table('notices as n')->join('test_assigns as ta','ta.id','=','n.test_assign_id')
            ->join('test_configs as tc','tc.id','=','ta.test_config_id')->whereIn('n.id',$id_arr)->select('n.id','tc.total_score')->get();
        $test_arr = array();
        foreach($test_rule as $_va){
            $test_arr[$_va->id] = $_va->total_score;
        }

        //查成绩
        $person = DB::table('test_score_records')
            ->whereIn('notice_id',$id_arr)
            ->where('student_id',$request['student'])
            ->where('test_status','=','1')
            ->where('test_score','>=',0)
            ->select('notice_id','test_score')
            ->get()->toArray();

        $classScore = array();
        $personScore = array();
        //构造数组

        //获取班级平均分
        $class = DB::table('test_score_records')
            ->where('test_score','>=',0)
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->where('test_status','=','1')
            ->orderBy('notice_id','ASC')
            ->select('test_score','notice_id')
            ->get();

        $class_arr = array();

        //单人缺考的部分用0补足
        $name_id_arr=array();
        foreach($class as $v){
            if(!isset($class_arr[$v->notice_id])){
                $class_arr[$v->notice_id]['score'] = 0;
                $class_arr[$v->notice_id]['num'] = 0;
            }
            $class_arr[$v->notice_id]['score'] += $v->test_score / $test_arr[$v->notice_id] * 100;
            $class_arr[$v->notice_id]['num'] += 1;
        }
        foreach($class_arr as $k => $v){
            $personScore[$k] = 0;
            $classScore[$k] = round($v['score']/$v['num'],2);
        }
        foreach($person as $v){
            $personScore[$v->notice_id] = $v->test_score / $test_arr[$v->notice_id] * 100;
        }

        //test_config_id
        foreach($test_ids as $v){
            $name_id_arr[] = $v->test_config_id;
        }


        //获取title
        $title = DB::table('test_configs')->whereIn('id',$name_id_arr)->get(['name'])->toArray();
        $data = array('person'=>$personScore,'class'=>$classScore,'title'=>$title);
        return array('status'=>1,'message'=>'success','data'=>$data);
    }

    public function get_tingxie(Request $request)
    {
        //找到听写
        $test_ids = DB::table('dictation_configs as dc')->join('notices as n','n.dictation_config_id','=','dc.id')
            ->where('n.notice_type','=','4')
            ->where('n.squad_id','=',$request['squad'])
            ->whereIn('n.status',['2','4'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)<='.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)>='.strtotime($request['startTime']))
            ->select('n.id as notice_id','dc.id as dictation_id','dc.dictation_desc')->get()->toArray();
        if(count($test_ids) <= 0){
            return array('status'=>-1,'message'=>'没有找到听写','data'=>'');
        }


        //获取到该班级的所有学生
        $student = DB::table('students')->where('squad_id',$request['squad'])->where('status','=','1')
            ->get(['id'])->toArray();
        $students = array();
        foreach($student as $v){
            $students[] = $v->id;
        }

        $id_arr = array();
        foreach($test_ids as $v){
            $id_arr[] = $v->notice_id;
        }

        //查成绩
        $person = DB::table('dictation_score_records')
            ->whereIn('notice_id',$id_arr)
            ->where('student_id',$request['student'])
            ->where('dictation_status','=','1')
            ->select('notice_id','dictation_score')
            ->get()->toArray();

        $classScore = array();
        $personScore = array();
        //构造数组

        //获取班级平均分
        $class = DB::table('dictation_score_records')
            ->where('dictation_status','=','1')
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->groupBy('notice_id')
            ->orderBy('notice_id','ASC')
            ->select(DB::raw('round(AVG(dictation_score),2) as score'),'notice_id')
            ->get()->toArray();

        //单人缺考的部分用0补足
        $title=array();
        foreach($class as $v){
            $personScore[$v->notice_id] = 0;
            $classScore[$v->notice_id] = $v->score;
        }
        foreach($person as $v){
            $personScore[$v->notice_id] = $v->dictation_score;
        }

        //获取title
        $i=1;
        foreach($test_ids as $v){
            $title[] = '第'.$i.'次';
            $i++;
        }

        $data = array('person'=>$personScore,'class'=>$classScore,'title'=>$title);
        return array('status'=>1,'message'=>'success','data'=>$data);
    }

    public function get_jiaxiao($request)
    {
        //找到家校
        $test_ids = DB::table('jiaxiao_records as j')->join('notices as n','n.id','=','j.notice_id')
            ->where('n.squad_id',$request['squad'])
            ->where('n.notice_type','=','3')
            ->whereIn('n.status',['2','4'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)<='.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)>='.strtotime($request['startTime']))
            ->select('n.id as notice_id')->get()->toArray();
        if(count($test_ids) <= 0){
            return array('status'=>-1,'message'=>'没有找到家校','data'=>'');
        }

        //获取到该班级的所有学生
        $student = DB::table('students')->where('squad_id',$request['squad'])->where('status','=','1')
            ->get(['id'])->toArray();
        $students = array();
        foreach($student as $v){
            $students[] = $v->id;
        }

        $id_arr = array();
        foreach($test_ids as $v){
            $id_arr[] = $v->notice_id;
        }

        $id_arr=array_unique($id_arr);

        //查成绩
        $person = DB::table('jiaxiao_records')
            ->whereIn('notice_id',$id_arr)
            ->where('student_id',$request['student'])
            ->where('jiaxiao_score','>=',0)
            ->select('notice_id','jiaxiao_score')
            ->get()->toArray();

        $classScore = array();
        $personScore = array();
        //构造数组

        //获取班级平均分
        $class = DB::table('jiaxiao_records')
            ->whereIn('jiaxiao_status',['1','3'])
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->groupBy('notice_id')
            ->orderBy('notice_id','ASC')
            ->select(DB::raw('round(AVG(jiaxiao_score),2) as score'),'notice_id')
            ->get()->toArray();

        //用于写评语
        $ForMemo = DB::table('jiaxiao_records')
            ->whereIn('jiaxiao_status',['1','3'])
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->groupBy('student_id')
            ->orderBy('score','ASC')
            ->select(DB::raw('round(AVG(jiaxiao_score),2) as score'),'student_id')
            ->get();

        //单人缺考的部分用0补足
        foreach($class as $v){
            $personScore[$v->notice_id] = 0;
            $classScore[$v->notice_id] = $v->score;
        }
        foreach($person as $v){
            $personScore[$v->notice_id] = $v->jiaxiao_score;
        }

        //test_config_id
        $i=1;
        $title=array();
        foreach($id_arr as $v){
            $title[] = '第'.$i.'次';
            $i++;
        }

        $score=array();
        $score['person']=$personScore;
        $score['class']=$classScore;
        /*-------------------------以上是获取家校分数，做折线图----------------------------*/
        /*-------------------------以下获取次数，做饼图-----------------------------------*/


        $personJson = $squadJson = '';
        $status = array('1'=>'准交','2'=>'未交','3'=>'补交');

        $person = DB::table('jiaxiao_records')
        ->whereIn('notice_id',$id_arr)
        ->where('student_id',$request['student'])
        ->groupBy('jiaxiao_status')
        ->select('jiaxiao_status',DB::raw('count(1) as num'))
        ->get()->toArray();

        $temp=array();
        foreach($person as $v){
            $temp[$status[$v->jiaxiao_status]] = $v->num;
        }
        $personJson = json_encode($temp);

        $class = DB::table('jiaxiao_records')
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->groupBy('jiaxiao_status')
            ->select('jiaxiao_status',DB::raw('count(1) as num'))
            ->get()->toArray();

        $temp=array();
        foreach($class as $v){
            $temp[$status[$v->jiaxiao_status]] = $v->num;
        }
        $classJson = json_encode($temp);

        //获取班段
        $squads = DB::table('squads')->where('section',$request['section'])->get(['id']);
        $squad_arr = array();
        foreach($squads as $v){
            $squad_arr[] = $v->id;
        }
        $section = DB::table('jiaxiao_records as jr')
            ->join('notices as n','jr.notice_id','=','n.id')
            ->whereIn('n.status',['2','4'])
            ->whereIn('n.squad_id',$squad_arr)
            ->groupBy('jr.jiaxiao_status')
            ->select('jr.jiaxiao_status',DB::raw('count(1) as num'))
            ->get()->toArray();
        $temp=array();
        foreach($section as $v){
            $temp[$status[$v->jiaxiao_status]] = $v->num;
        }
        $sectionJson = json_encode($temp);

        $json = array();
        $json['person'] = $personJson;
        $json['class'] = $classJson;
        $json['section'] = $sectionJson;


        $memo = array();
        $memo[] = '在'.date('Y/m/d',strtotime($request['startTime'])).'-'.date('Y/m/d',strtotime($request['endTime'])).'时间段内，共交家校{blue|'.($i-1).'}次';

        $memoCount = $nowCount = 0;
        foreach($ForMemo as $v){
            $memoCount ++;
        }
        foreach($ForMemo as $v){
            if($v->student_id != $request['student']){
                $nowCount++;
            }else{
                $memo[] = '你所有家校的平均分为{red|'.$v->score.'},超过了{red|'.round($nowCount/($memoCount-1)*100,2).'%}的同班同学';
                break;
            }
        }
        //获取title
        $data = array('score'=>$score,'data'=>$json,'title'=>$title,'memo'=>$memo);
        return array('status'=>1,'message'=>'success','data'=>$data);
    }

    public function get_zuoye($request)
    {
        //找到家校
        $test_ids = DB::table('zuoye_records as j')->join('notices as n','n.id','=','j.notice_id')
            ->where('n.notice_type','=','5')
            ->where('n.squad_id','=',$request['squad'])
            ->whereIn('n.status',['2','4'])
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)<='.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(n.starttime)>='.strtotime($request['startTime']))
            ->select('n.id as notice_id')->get()->toArray();
        if(count($test_ids) <= 0){
            return array('status'=>-1,'message'=>'没有找到作业','data'=>'');
        }

        //获取到该班级的所有学生
        $student = DB::table('students')->where('squad_id',$request['squad'])->where('status','=','1')
            ->get(['id'])->toArray();
        $students = array();
        foreach($student as $v){
            $students[] = $v->id;
        }

        $id_arr = array();
        foreach($test_ids as $v){
            $id_arr[] = $v->notice_id;
        }
        $id_arr = array_unique($id_arr);

        //查成绩
        $person = DB::table('zuoye_records')
            ->whereIn('notice_id',$id_arr)
            ->where('student_id',$request['student'])
            ->whereIn('zuoye_status',['1','3'])
            ->select('notice_id','zuoye_score')
            ->get()->toArray();

        $classScore = array();
        $personScore = array();
        //构造数组

        //获取班级平均分
        $class = DB::table('zuoye_records')
            ->whereIn('zuoye_status',['1','3'])
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->groupBy('notice_id')
            ->orderBy('notice_id','ASC')
            ->select(DB::raw('round(AVG(zuoye_score),2) as score'),'notice_id')
            ->get()->toArray();

        //用于填写评语
        $ForMemo = DB::table('zuoye_records')
            ->whereIn('zuoye_status',['1','3'])
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->groupBy('student_id')
            ->orderBy('score','ASC')
            ->select(DB::raw('round(AVG(zuoye_score),2) as score'),'student_id')
            ->get()->toArray();


        //单人缺考的部分用0补足
        foreach($class as $v){
            $personScore[$v->notice_id] = 0;
            $classScore[$v->notice_id] = $v->score;
        }
        foreach($person as $v){
            $personScore[$v->notice_id] = $v->zuoye_score;
        }

        //test_config_id
        $i=1;
        $title=array();
        foreach($id_arr as $v){
            $title[] = '第'.$i.'次';
            $i++;
        }

        $score=array();
        $score['person']=$personScore;
        $score['class']=$classScore;
        /*-------------------------以上是获取家校分数，做折线图----------------------------*/
        /*-------------------------以下获取次数，做饼图-----------------------------------*/


        $personJson = $squadJson = '';
        $status = array('1'=>'准交','2'=>'未交','3'=>'补交');

        $person = DB::table('zuoye_records')
            ->whereIn('notice_id',$id_arr)
            ->where('student_id',$request['student'])
            ->groupBy('zuoye_status')
            ->select('zuoye_status',DB::raw('count(1) as num'))
            ->get()->toArray();

        $temp=array();
        foreach($person as $v){
            $temp[$status[$v->zuoye_status]] = $v->num;
        }
        $personJson = json_encode($temp);

        $class = DB::table('zuoye_records')
            ->whereIn('notice_id',$id_arr)
            ->whereIn('student_id',$students)
            ->groupBy('zuoye_status')
            ->select('zuoye_status',DB::raw('count(1) as num'))
            ->get()->toArray();

        $temp=array();
        foreach($class as $v){
            $temp[$status[$v->zuoye_status]] = $v->num;
        }
        $classJson = json_encode($temp);

        //获取班段
        $squads = DB::table('squads')->where('section',$request['section'])->get(['id']);
        $squad_arr = array();
        foreach($squads as $v){
            $squad_arr[] = $v->id;
        }
        $section = DB::table('zuoye_records as jr')
            ->join('notices as n','jr.notice_id','=','n.id')
            ->whereIn('n.status',['2','4'])
            ->whereIn('n.squad_id',$squad_arr)
            ->groupBy('jr.zuoye_status')
            ->select('jr.zuoye_status',DB::raw('count(1) as num'))
            ->get()->toArray();
        $temp=array();
        foreach($section as $v){
            $temp[$status[$v->zuoye_status]] = $v->num;
        }
        $sectionJson = json_encode($temp);

        $json = array();
        $json['person'] = $personJson;
        $json['class'] = $classJson;
        $json['section'] = $sectionJson;


        $memo = array();
        $memo[] = '在'.date('Y/m/d',strtotime($request['startTime'])).'-'.date('Y/m/d',strtotime($request['endTime'])).'时间段内，共交作业{blue|'.($i-1).'}次';

        $memoCount = $nowCount = 0;
        foreach($ForMemo as $v){
            $memoCount ++;
        }
        foreach($ForMemo as $v){
            if($v->student_id != $request['student']){
                $nowCount++;
            }else{
                $memo[] = '你所有作业的平均分为{red|'.$v->score.'},超过了{red|'.round($nowCount/($memoCount-1)*100,2).'%}的同班同学';
                break;
            }
        }


        //获取title
        $data = array('score'=>$score,'data'=>$json,'title'=>$title,'memo'=>$memo);
        return array('status'=>1,'message'=>'success','data'=>$data);
    }

    public function get_kaoqin_stu($request)
    {
        $kaoqin = DB::table('attendance_records')->where('student_id',$request['student'])
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) <= '.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) >= '.strtotime($request['startTime']))
            ->groupBy('attendance_status')
            ->select('attendance_status',DB::raw('count(1) as num'))
            ->get();

        $status = array('1'=>'正常','2'=>'请假','3'=>'迟到','4'=>'缺勤');

        $json=array();
        foreach($status as $v){
            $json[$v] = 0;
        }

        foreach($kaoqin as $v){
            $json[$status[$v->attendance_status]] = $v->num;
        }

        return $json;
    }

    public function get_tingxie_stu($request)
    {
        $tingxie = DB::table('dictation_score_records')->where('student_id',$request['student'])
            ->where('dictation_status','=','1')
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) <= '.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) >= '.strtotime($request['startTime']))
            ->select('dictation_score','created_at')
            ->get();

        $title=$data = array();
        foreach($tingxie as $v){
            $title[] = date('Y-m-d',strtotime($v->created_at));
            $data[] = $v->dictation_score;
        }

        return array('data'=>$data,'title'=>$title);
    }

    public function get_ceyan_stu($request)
    {
        $tingxie = DB::table('test_score_records')->where('student_id',$request['student'])
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) <= '.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) >= '.strtotime($request['startTime']))
            ->select('test_score','created_at')
            ->get();

        $title=$data = array();
        foreach($tingxie as $v){
            $title[] = date('Y-m-d',strtotime($v->created_at));
            $data[] = $v->test_score;
        }

        return array('data'=>$data,'title'=>$title);
    }

    public function get_jiaxiao_stu($request)
    {
        $jiaxiao = DB::table('jiaxiao_records')->where('student_id',$request['student'])
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) <= '.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) >= '.strtotime($request['startTime']))
            ->groupBy('jiaxiao_status')
            ->select('jiaxiao_status',DB::raw('count(1) as num'))
            ->get();
        $status = array('1'=>'准交','2'=>'未交','3'=>'补交');

        $json=array();
        foreach($status as $v){
            $json[$v] = 0;
        }

        foreach($jiaxiao as $v){
            $json[$status[$v->jiaxiao_status]] = $v->num;
        }

        $jiaxiao = DB::table('jiaxiao_records')->where('student_id',$request['student'])
            ->whereIn('jiaxiao_status',['1','3'])
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) <= '.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) >= '.strtotime($request['startTime']))
            ->select('jiaxiao_score','created_at')
            ->get();


        $title=$data = array();
        foreach($jiaxiao as $v){
            $title[] = date('Y-m-d',strtotime($v->created_at));
            $data[] = $v->jiaxiao_score;
        }

        return array('data'=>$data,'title'=>$title,'json'=>$json);
    }

    public function get_zuoye_stu($request)
    {
        $zuoye = DB::table('zuoye_records')->where('student_id',$request['student'])
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) <= '.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) >= '.strtotime($request['startTime']))
            ->groupBy('zuoye_status')
            ->select('zuoye_status',DB::raw('count(1) as num'))
            ->get();
        $status = array('1'=>'准交','2'=>'未交','3'=>'补交');

        $json=array();
        foreach($status as $v){
            $json[$v] = 0;
        }

        foreach($zuoye as $v){
            $json[$status[$v->zuoye_status]] = $v->num;
        }

        $zuoye = DB::table('zuoye_records')->where('student_id',$request['student'])
            ->whereIn('zuoye_status',['1','3'])
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) <= '.strtotime($request['endTime']))
            ->whereRaw('UNIX_TIMESTAMP(`created_at`) >= '.strtotime($request['startTime']))
            ->select('zuoye_score','created_at')
            ->get();


        $title=$data = array();
        foreach($zuoye as $v){
            $title[] = date('Y-m-d',strtotime($v->created_at));
            $data[] = $v->zuoye_score;
        }

        return array('data'=>$data,'title'=>$title,'json'=>$json);
    }
}
